/**
 * PrepareCursorHelper.java [V 1.0.0]
 * classes : com.xiaocq.iwordkiller.db.PrepareCursorHelper
 * xiaocq Create at 2012-4-10 下午2:00:44
 */
package com.xiaocq.iwordkiller.db;

import java.io.BufferedReader;
import java.io.FileNotFoundException;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.xiaocq.iwordkiller.db.VocabularyDatabase.VocabularyMeta;
import com.xiaocq.iwordkiller.file.FileIOHelper;

/**
 * com.xiaocq.iwordkiller.db.PrepareCursorHelper
 * 
 * @author Mystery Create at 2012-4-10 下午2:00:44
 */
public class PrepareCursorHelper {
	private DatabaseHelper mOpenHelper;
	private SQLiteDatabase db;
	private Context context;

	private static final String[] COLUMNS = new String[] { VocabularyMeta._ID,
			VocabularyMeta.VOCABULARY, VocabularyMeta.EXPLAIN };

	public PrepareCursorHelper(Context context) {
		this.context = context;
	}

	public void open() {
		mOpenHelper = new DatabaseHelper(context);
		db = mOpenHelper.getReadableDatabase();
	}

	public void close() {
		if (db != null) {
			db.close();
		}
		mOpenHelper.close();
	}
	
	public Cursor getTestDataByTestId(int testId){
		if(testId < 51){
			return getUnitData(testId, false);
		}else{
			return getAllData(testId);
		}
	}

	/**
	 * 根据输入的类型参数，返回指定类型的单词集
	 * 
	 * @param typeParam
	 * @return 包含指定单词数据的Cursor
	 */
	public Cursor getAllData(int typeParam) {
		Cursor cursor;
		String selection = null;
		switch (typeParam) {
		case 51:
			break;
		case 52:
			selection = VocabularyMeta.ANSWER_STATE + " = 0";
			break;
		case 53:
			selection = VocabularyMeta.ANSWER_STATE + " != 0";
			break;
		case 54:
			selection = VocabularyMeta.ANSWER_STATE + " = 1";
			break;
		case 55:
			selection = VocabularyMeta.ANSWER_STATE + " = 2";
			break;
		default:
			selection = null;
		}
		cursor = db.query(VocabularyMeta.TABLE_NAME, COLUMNS, selection, null,
				null, null, null);
		return cursor;
	}

	/**
	 * 根据输入的单元Id以及指定是否为错词来查询 响应的单词集
	 * 
	 * @param unitId
	 * @param isWrong
	 * @return 包含指定单词数据的Cursor
	 */
	public Cursor getUnitData(int unitId, boolean isWrong) {
		Cursor cursor;
		String selection = VocabularyMeta.BELONG_TO + " = " + unitId;
		if (isWrong) {
			selection += " AND " + VocabularyMeta.ANSWER_STATE + " = 2";
		}
		cursor = db.query(VocabularyMeta.TABLE_NAME, COLUMNS, selection, null,
				null, null, null);
		return cursor;
	}

	/**
	 * 根据指定的类型获取对应类型的单词数量
	 * 
	 * @param typeParam
	 * @return 指定类型的单词数量
	 */
	public int getAllCounts(int typeParam) {
		String sql = "SELECT COUNT(*) FROM " + VocabularyMeta.TABLE_NAME;
		int counts;
		Cursor cursor;
		switch (typeParam) {
		case 51:
			break;
		case 52:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " = 0";
			break;
		case 53:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " != 0";
			break;
		case 54:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " = 1";
			break;
		case 55:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " = 2";
			break;
		}

		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		counts = cursor.getInt(0);
		return counts;
	}

	/**
	 * 根据输入的单元Id以及指定是否为错词来获得单词数量
	 * 
	 * @param unitId
	 * @param isWrong
	 * @return 返回指定条件下的单词数量
	 */
	public int getUnitCounts(int unitId, boolean isWrong) {
		int counts;
		Cursor cursor;
		String sql = "SELECT COUNT(*) FROM " + VocabularyMeta.TABLE_NAME
				+ " WHERE " + VocabularyMeta.BELONG_TO + " = " + unitId;
		if (isWrong) {
			sql += " AND " + VocabularyMeta.ANSWER_STATE + " = 2";
		}

		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		counts = cursor.getInt(0);
		return counts;
	}

	/**
	 * 根据传入的testId的值的不同返回特定的数据集
	 * @param testId
	 * @return
	 */
	public Cursor getRowIdSetByTestId(int testId){
		if(testId <51){
			return getIdsOfUnit(testId);
		}else{
			return getIdsOfType(testId);
		}
	}
	
	/**
	 * 根据指定的unitId返回一个包含了该单元在所有单词中的rowId的Cursor
	 * 
	 * @param unitId
	 * @return 包含了该单元在所有单词中的rowId的Cursor
	 */
	public Cursor getIdsOfUnit(int unitId) {
		Cursor cursor;
		final String sql = "SELECT _id FROM " + VocabularyMeta.TABLE_NAME + " WHERE "
				+ VocabularyMeta.BELONG_TO + " = " + unitId;
		cursor = db.rawQuery(sql, null);
		return cursor;
	}
	
	/**
	 * 根据type的值返回指定的包含id数据集
	 * @param type
	 * @return
	 */
	public Cursor getIdsOfType(int type){
		Cursor cursor;
		String sql = "SELECT _id FROM " + VocabularyMeta.TABLE_NAME;
		switch(type){
		case 51:
			break;
		case 52:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " = 0";
			break;
		case 53:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " != 0";
			break;
		case 54:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " = 1";
			break;
		case 55:
			sql += " WHERE " + VocabularyMeta.ANSWER_STATE + " = 2";
			break;
		}
		cursor = db.rawQuery(sql, null);
		return cursor;
	}
	
	/**
	 * 根据当前测试完成的时间，获取本次错误的错词
	 * @param currentTestFinishTimeInMillis
	 * @return
	 */
	public Cursor getWrongDataOfTest(long currentTestFinishTimeInMillis){
		String sql = VocabularyMeta.ANSWER_STATE + " = 2 AND "
				+ VocabularyMeta.LEAST_ACCESS + " = " + currentTestFinishTimeInMillis;
		Cursor cursor = db.query(VocabularyMeta.TABLE_NAME, COLUMNS, sql, null,
				null, null, null);
		return cursor;
	}

	/**
	 * 重置数据库状态
	 */
	public void resetDatabase() {
		final String sql = "UPDATE " + VocabularyMeta.TABLE_NAME + " SET "
				+ VocabularyMeta.ANSWER_STATE + " = 0,"
				+ VocabularyMeta.TEST_COUNT + " = 0,"
				+ VocabularyMeta.CORRECT_COUNT + " = 0";
		db.execSQL(sql);
	}
	
	/**
	 * 初始化数据库，执行导入
	 */
	public void initDatabase(){
		FileIOHelper helper = new FileIOHelper(context);
		final String sql = "INSERT INTO " + VocabularyMeta.TABLE_NAME + " ("
				+ VocabularyMeta.VOCABULARY + "," + VocabularyMeta.EXPLAIN
				+ "," + VocabularyMeta.TEST_COUNT + ","
				+ VocabularyMeta.CORRECT_COUNT + ","
				+ VocabularyMeta.ANSWER_STATE + "," + VocabularyMeta.BELONG_TO
				+ "," + VocabularyMeta.LEAST_ACCESS + ") VALUES(?,?,0,0,0,?,0)";
		String[] fileds;
		SQLiteStatement stmt;
		try {
			BufferedReader reader = helper.getBufferedReaderFromAssets("AllWords.txt");
			stmt = db.compileStatement(sql);
			db.beginTransaction();
			while(reader.ready()){
				fileds = reader.readLine().split("@");
				
				stmt.bindString(1, fileds[0]);
				stmt.bindString(2, fileds[1]);
				stmt.bindString(3, fileds[2]);
				
				stmt.execute();
			}
			stmt.close();
			db.setTransactionSuccessful();
			reader.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.endTransaction();
		}
	}
	
	/**
	 * 根据数据集更新数据库,返回错误个数
	 * @param records
	 * @param currentTimeInMillis
	 * @return
	 */
	public int updateDatabase(int[] records, long currentTimeInMillis){
		final String sql = "UPDATE " + VocabularyMeta.TABLE_NAME + " SET "
				+ VocabularyMeta.ANSWER_STATE + " = ?, "
				+ VocabularyMeta.LEAST_ACCESS + " = ? WHERE "
				+ VocabularyMeta._ID + " = ?";
		int length = records.length;
		int isCorrect,rowId;
		int wrongCounts = 0;
		SQLiteStatement stmt = db.compileStatement(sql);
		
		stmt.bindLong(2, currentTimeInMillis);
		try{
			db.beginTransaction();
			for(int i=0; i < length; i++){
				rowId = records[i] / 10;
				isCorrect = records[i] % 10;
				if(isCorrect == 2){
					wrongCounts++;
				}
				stmt.bindLong(1, isCorrect);
				stmt.bindLong(3, rowId);
				
				stmt.execute();
			}
			db.setTransactionSuccessful();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.endTransaction();
		}
		return wrongCounts;
	}
}
